# ✅ scholar_service.py
from sqlalchemy.orm import Session
from sqlalchemy import func, and_,or_,distinct,case
from typing import Optional
from sqlalchemy.orm import aliased
from datetime import datetime, timedelta
import re
from typing import List, Dict
from dateutil.relativedelta import relativedelta
from datetime import date
from app.models.iaro_career.scholars import Scholar
from app.models.iaro_career.scholar_status import ScholarStatus
from app.models.iaro_career.dietary_preference import DietaryPreferences
from app.models.iaro_career.ite_detail import ITEDetail
from app.models.iaro_career.ite_supervisor import ITESupervisor
from app.models.iaro_career.ite_dashboard_manual import ITEDashboardManual
from app.models.iaro_career.program_date import ProgramDate
from app.models.iaro_career.gateway_school import GatewaySchool
from app.models.iaro_career.personal_eval import PersonalEvaluationResponse
from typing import Dict, List
from sqlalchemy.orm import Session
from app.models.iaro_coop_admin.program import Program as CoopProgram
from app.schema.scholar_schema import ScholarFullResponse
from app.schema.dashboard_manual_schema import ITEDashboardManualUpdate
from typing import Dict, List, Optional
from datetime import date

from sqlalchemy import or_, and_, func, cast, String
from sqlalchemy.orm import aliased, Session

def list_scholar_full_paginated(db: Session, skip: int = 0, limit: int = 20) -> Dict:
    # ✅ Supervisor aggregation subquery
    supervisor_subq = (
        db.query(
            ITESupervisor.ite_id.label("ite_id"),
            func.listagg(
                ITESupervisor.supervisor_name + ' (' + ITESupervisor.supervisor_title + ')',
                '; '
            ).within_group(ITESupervisor.supervisor_name).label("supervisors")
        )
        .group_by(ITESupervisor.ite_id)
    ).subquery()

    # ✅ For each scholar, get the latest submission time for survey 41/42/43
    #   If you want the earliest, change func.max to func.min
    pd_per_survey_subq = (
        db.query(
            PersonalEvaluationResponse.scholar_id.label("scholar_id"),
            func.max(
                case(
                    (PersonalEvaluationResponse.per_eva_survey_id == 41, PersonalEvaluationResponse.ans_timestamp),
                    else_=None
                )
            ).label("pd41_ts"),
            func.max(
                case(
                    (PersonalEvaluationResponse.per_eva_survey_id == 42, PersonalEvaluationResponse.ans_timestamp),
                    else_=None
                )
            ).label("pd42_ts"),
            func.max(
                case(
                    (PersonalEvaluationResponse.per_eva_survey_id == 43, PersonalEvaluationResponse.ans_timestamp),
                    else_=None
                )
            ).label("pd43_ts"),
        )
        .filter(PersonalEvaluationResponse.per_eva_survey_id.in_([41, 42, 43]))
        .group_by(PersonalEvaluationResponse.scholar_id)
    ).subquery()

    # ✅ Placement Grade (keep original logic)
    placement_grade_subq = (
        db.query(
            PersonalEvaluationResponse.scholar_id.label("scholar_id"),
            func.max(PersonalEvaluationResponse.ans_response).label("placement_grade")
        )
        .filter(PersonalEvaluationResponse.question_id == 121)
        .group_by(PersonalEvaluationResponse.scholar_id)
    ).subquery()

    # ✅ Student Comment (keep original logic)
    student_comment_subq = (
        db.query(
            PersonalEvaluationResponse.scholar_id.label("scholar_id"),
            func.max(PersonalEvaluationResponse.ans_response).label("student_comment")
        )
        .filter(PersonalEvaluationResponse.question_id == 125)
        .group_by(PersonalEvaluationResponse.scholar_id)
    ).subquery()

    # ===============================
    # Main query
    # ===============================
    base_query = (
        db.query(
            # —— ITE fields ——
            ITEDetail.ite_id,
            ITEDetail.scholar_id,
            ITEDetail.sponsor_name,
            ITEDetail.department,
            ITEDetail.ite_number,
            ITEDetail.prst_date,
            ITEDetail.ite_start,
            ITEDetail.ite_finish,
            ITEDetail.sponsor_evl_date,
            ITEDetail.completed_evl_date,
            ITEDetail.completed_form_date,
            ITEDetail.ite_detail_date,
            ITEDetail.ite_prst_date,
            ITEDetail.cocode,

            # —— Aggregated/subquery fields ——
            supervisor_subq.c.supervisors,
            ITEDashboardManual.indigenous_student,
            ITEDashboardManual.placement_performance,
            ITEDashboardManual.grade_updated,
            ITEDashboardManual.grade_updated_date,
            ITEDashboardManual.unsw_attendee_presentation,
            ITEDashboardManual.mpc_submitted_date,
            ITEDashboardManual.mpc_submitted_on_time,
            ITEDashboardManual.mpc_call_date,
            ITEDashboardManual.mpc_call_on_time,
            ITEDashboardManual.it_change_approved,

            # Original three columns for debugging (can keep)
            pd_per_survey_subq.c.pd41_ts.label("pd41_ts"),
            pd_per_survey_subq.c.pd42_ts.label("pd42_ts"),
            pd_per_survey_subq.c.pd43_ts.label("pd43_ts"),

            # ✅ Final column for PD submission date, select by ITE number
            case(
                (ITEDetail.ite_number == 1, pd_per_survey_subq.c.pd41_ts),
                (ITEDetail.ite_number == 2, pd_per_survey_subq.c.pd42_ts),
                (ITEDetail.ite_number == 3, pd_per_survey_subq.c.pd43_ts),
                else_=None
            ).label("pd_ts"),

            placement_grade_subq.c.placement_grade,
            student_comment_subq.c.student_comment,

            # —— Scholar fields ——
            Scholar.first_name,
            Scholar.last_name,
            Scholar.preferred_name,
            Scholar.student_id,
            Scholar.email,
            Scholar.gender,
            Scholar.date_of_birth,
            Scholar.program_id,
            Scholar.status_id,
            Scholar.school_code,
            Scholar.dietary_pref_id,
            Scholar.dietary_allergy,
            Scholar.dietary_religious,
            Scholar.medical_allergies,
            Scholar.program_start_date,
            Scholar.program_finish_date,
            Scholar.current_email,

            ScholarStatus.status_description,
            DietaryPreferences.dietary_pref_desc,

            ProgramDate.program_code,
            ProgramDate.program_year,
            ProgramDate.it1_start_date,
            ProgramDate.it1_weeks,
            ProgramDate.it2_start_date,
            ProgramDate.it2_weeks,
            ProgramDate.it3_start_date,
            ProgramDate.it3_weeks,

            GatewaySchool.school_id,
            GatewaySchool.school_name,
            GatewaySchool.is_gateway,
        )
        .select_from(ITEDetail)  # Use ITE as root
        .join(Scholar, ITEDetail.scholar_id == Scholar.scholar_id)
        .outerjoin(ScholarStatus, Scholar.status_id == ScholarStatus.status_id)
        .outerjoin(DietaryPreferences, Scholar.dietary_pref_id == DietaryPreferences.dietary_pref_id)
        .outerjoin(ProgramDate, Scholar.program_id == ProgramDate.program_code)
        .outerjoin(CoopProgram, Scholar.program_id == CoopProgram.program_code)
        .outerjoin(GatewaySchool, Scholar.school_code == GatewaySchool.school_id)
        .outerjoin(supervisor_subq, ITEDetail.ite_id == supervisor_subq.c.ite_id)
        .outerjoin(ITEDashboardManual, ITEDetail.ite_id == ITEDashboardManual.ite_id)
        # ✅ Use new per-survey subquery to replace original latest_eval_subq
        .outerjoin(pd_per_survey_subq, Scholar.scholar_id == pd_per_survey_subq.c.scholar_id)
        .outerjoin(placement_grade_subq, Scholar.scholar_id == placement_grade_subq.c.scholar_id)
        .outerjoin(student_comment_subq, Scholar.scholar_id == student_comment_subq.c.scholar_id)
    )

    results = (
        base_query
        .order_by(Scholar.scholar_id, ITEDetail.ite_id)
        .offset(skip)
        .limit(limit)
        .all()
    )

    total = (
        db.query(func.count())
        .select_from(base_query.subquery())
        .scalar()
    )

    response = []

    for row in results:
        program_year = row.program_year
        start_date = row.program_start_date

        from datetime import date, timedelta
        from dateutil.relativedelta import relativedelta

        scholar_type = None
        if start_date and program_year:
            feb_12 = date(program_year, 2, 12)
            aug_12 = date(program_year, 8, 12)

            if start_date.year > program_year:
                scholar_type = "REPLACEMENT"
            elif start_date <= feb_12:
                scholar_type = "ORIGINAL"
            elif start_date <= aug_12:
                scholar_type = "LATE APPOINTED"
            else:
                scholar_type = "LATE"

        ite_number = row.ite_number
        if ite_number == 1:
            latest_start_date = row.it1_start_date
            program_weeks = row.it1_weeks
        elif ite_number == 2:
            latest_start_date = row.it2_start_date
            program_weeks = row.it2_weeks
        elif ite_number == 3:
            latest_start_date = row.it3_start_date
            program_weeks = row.it3_weeks
        else:
            latest_start_date = None
            program_weeks = None

        if latest_start_date and program_weeks is not None:
            expected_finish_date = latest_start_date + timedelta(weeks=program_weeks)
        else:
            expected_finish_date = None

        if latest_start_date:
            latest_start_date = latest_start_date.date()
        if expected_finish_date:
            expected_finish_date = expected_finish_date.date()

        ite_start_date = row.ite_start
        if ite_start_date and latest_start_date:
            start_date_met = "YES" if ite_start_date <= latest_start_date + timedelta(weeks=1) else "NO"
        else:
            start_date_met = None

        ite_end_date = row.ite_finish
        if ite_end_date and expected_finish_date:
            end_date_met = "YES" if ite_end_date <= expected_finish_date + timedelta(weeks=1) else "NO"
        else:
            end_date_met = None

        # ✅ Use per-survey time instead of latest eval time
        pd_agreement_submission_date = row.pd_ts

        if ite_start_date and pd_agreement_submission_date:
            deadline = ite_start_date + timedelta(weeks=3)
            pd_on_time = "YES" if pd_agreement_submission_date <= deadline else "NO"
        else:
            pd_on_time = None

        if ite_start_date is None:
            placement_midpoint = None
        elif ite_number == 1:
            placement_midpoint = ite_start_date + relativedelta(months=2)
        elif program_weeks and program_weeks > 0:
            placement_midpoint = ite_start_date + timedelta(weeks=program_weeks / 2)
        else:
            placement_midpoint = None

        mpc_call_date = row.mpc_call_date
        mpc_due_date = mpc_call_date + timedelta(weeks=1) if mpc_call_date else None
        end_of_it_input_date = row.ite_prst_date
        end_of_it_presentation_date = row.prst_date
        if end_of_it_input_date and end_of_it_presentation_date:
            deadline = end_of_it_presentation_date + timedelta(weeks=2)
            it_presentation_ontime = "YES" if end_of_it_input_date <= deadline else "NO"
        else:
            it_presentation_ontime = None

        sponsor_sent_date = row.sponsor_evl_date
        ite_end_date = row.ite_finish
        if sponsor_sent_date and ite_end_date:
            sent_before_end = "YES" if sponsor_sent_date < ite_end_date else "NO"
        else:
            sent_before_end = None

        placement_completed_date = row.completed_evl_date
        if sponsor_sent_date and placement_completed_date:
            sponsor_eval_ontime = sponsor_sent_date < placement_completed_date
        else:
            sponsor_eval_ontime = None

        response.append(ScholarFullResponse(
            program=str(row.program_id) if row.program_id is not None else None,
            year=row.program_year,
            student_number=row.student_id,
            first_name=row.first_name,
            preferred_name=row.preferred_name,
            last_name=row.last_name,
            default_email=row.email,
            alternate_email=row.current_email,
            gender=row.gender,
            date_of_birth=row.date_of_birth,
            school_code=row.school_id,
            school_name=row.school_name,
            gateway_school="YES" if row.is_gateway == "Y" else "NO",
            indigenous_student=row.indigenous_student,
            university_status=row.status_description,
            start_date=row.program_start_date,
            end_date=row.program_finish_date,
            scholar_type=scholar_type,
            dietary_preference=row.dietary_pref_desc,
            dietary_allergies=row.dietary_allergy,
            dietary_religious=row.dietary_religious,
            medical_allergies=row.medical_allergies,
            ite_number=ite_number,
            sponsor_name=row.sponsor_name,
            department=row.department,
            supervisors=row.supervisors,
            ite_start=row.ite_start,
            ite_end=row.ite_finish,
            ite_details_input_date=row.ite_detail_date,
            latest_start_date=latest_start_date,
            expected_finish_date=expected_finish_date,
            start_date_met=start_date_met,
            end_date_met=end_date_met,
            it_change_approved=row.it_change_approved,
            pd_agreement_submission_date=pd_agreement_submission_date,  
            ite_input_in_time=pd_on_time,
            placement_mid_point=placement_midpoint,
            mpc_call_date=mpc_call_date,
            mpc_call_on_time=row.mpc_call_on_time,
            mpc_document_due_date=mpc_due_date,
            mpc_submitted_date=row.mpc_submitted_date,
            mpc_submitted_on_time=row.mpc_submitted_on_time,
            end_of_it_presentation_date=end_of_it_presentation_date,
            end_of_it_input_date=end_of_it_input_date,
            it_presentation_completed_ontime=it_presentation_ontime,
            unsw_attendee_on_presentation=row.unsw_attendee_presentation,
            sponsor_evaluation_sent_to_sponsor_date=sponsor_sent_date,
            sent_before_end_of_placement=sent_before_end,
            sponsor_completed_date=row.completed_form_date,
            placement_completed_date=placement_completed_date,
            placement_grade=row.placement_grade,
            placement_performance=row.placement_performance,
            grade_updated=row.grade_updated,
            grade_updated_date=row.grade_updated_date,
            scholar_comment=row.student_comment,
            sponsor_evaluation_completed_on_time=sponsor_eval_ontime
        ))

    return {
        "total": total,
        "scholars": response
    }



def list_scholar_full_paginated_with_search(
    db: Session,
    skip: int = 0,
    limit: int = 20,
    keywords: Optional[str] = None,
    start_date_input: Optional[date] = None,
    end_date_input: Optional[date] = None
) -> Dict:

    supervisor_subq = (
        db.query(
            ITESupervisor.ite_id.label("ite_id"),
            func.listagg(
                ITESupervisor.supervisor_name + ' (' + ITESupervisor.supervisor_title + ')',
                '; '
            ).within_group(ITESupervisor.supervisor_name).label("supervisors")
        )
        .group_by(ITESupervisor.ite_id)
    ).subquery()


    pd_per_survey_subq = (
        db.query(
            PersonalEvaluationResponse.scholar_id.label("scholar_id"),
            func.max(
                case(
                    (PersonalEvaluationResponse.per_eva_survey_id == 41, PersonalEvaluationResponse.ans_timestamp),
                    else_=None
                )
            ).label("pd41_ts"),
            func.max(
                case(
                    (PersonalEvaluationResponse.per_eva_survey_id == 42, PersonalEvaluationResponse.ans_timestamp),
                    else_=None
                )
            ).label("pd42_ts"),
            func.max(
                case(
                    (PersonalEvaluationResponse.per_eva_survey_id == 43, PersonalEvaluationResponse.ans_timestamp),
                    else_=None
                )
            ).label("pd43_ts"),
        )
        .filter(PersonalEvaluationResponse.per_eva_survey_id.in_([41, 42, 43]))
        .group_by(PersonalEvaluationResponse.scholar_id)
    ).subquery()

    placement_grade_subq = (
        db.query(
            PersonalEvaluationResponse.scholar_id.label("scholar_id"),
            func.max(PersonalEvaluationResponse.ans_response).label("placement_grade")
        )
        .filter(PersonalEvaluationResponse.question_id == 121)
        .group_by(PersonalEvaluationResponse.scholar_id)
    ).subquery()

    student_comment_subq = (
        db.query(
            PersonalEvaluationResponse.scholar_id.label("scholar_id"),
            func.max(PersonalEvaluationResponse.ans_response).label("student_comment")
        )
        .filter(PersonalEvaluationResponse.question_id == 125)
        .group_by(PersonalEvaluationResponse.scholar_id)
    ).subquery()


    ite_filter_q = (
        db.query(ITEDetail.ite_id)
        .join(Scholar, Scholar.scholar_id == ITEDetail.scholar_id)
        .outerjoin(GatewaySchool, Scholar.school_code == GatewaySchool.school_id)
    )

    if keywords:
        cleaned = re.sub(r'\s+', ' ', keywords.strip())
        tokens = [t.lower() for t in cleaned.split(' ') if t]
        pattern = f"%{cleaned.lower()}%"

        base_conditions = [
            func.lower(Scholar.first_name).like(pattern),
            func.lower(Scholar.last_name).like(pattern),
            func.lower(Scholar.preferred_name).like(pattern),
            func.lower(Scholar.email).like(pattern),
            func.lower(Scholar.current_email).like(pattern),
            func.lower(Scholar.student_id).like(pattern),
            func.lower(GatewaySchool.school_name).like(pattern),
            func.lower(ITEDetail.sponsor_name).like(pattern),
            func.lower(ITEDetail.department).like(pattern),
        ]

        if len(tokens) >= 2:
            first_candidate = tokens[0]
            last_candidate = ' '.join(tokens[1:])
            full_name_conditions = [
                and_(
                    func.lower(Scholar.first_name) == first_candidate,
                    func.lower(Scholar.last_name).like(f"%{last_candidate}%")
                ),
                and_(
                    func.lower(Scholar.last_name) == first_candidate,
                    func.lower(Scholar.first_name).like(f"%{last_candidate}%")
                ),
            ]
            match_condition = or_(*base_conditions, *full_name_conditions)
        else:
            match_condition = or_(*base_conditions)

        ite_filter_q = ite_filter_q.filter(match_condition)

    if start_date_input and end_date_input:
        ite_filter_q = ite_filter_q.filter(
            ITEDetail.ite_start >= start_date_input,
            ITEDetail.ite_finish <= end_date_input
        )
    elif start_date_input:
        ite_filter_q = ite_filter_q.filter(ITEDetail.ite_finish >= start_date_input)
    elif end_date_input:
        ite_filter_q = ite_filter_q.filter(ITEDetail.ite_start <= end_date_input)

    ite_ids_subq = ite_filter_q.with_entities(
        distinct(ITEDetail.ite_id).label("ite_id")
    ).subquery()

    total = db.query(func.count()).select_from(ite_ids_subq).scalar()

    page_ite_ids = [
        r.ite_id
        for r in db.query(ite_ids_subq.c.ite_id)
        .order_by(ite_ids_subq.c.ite_id)
        .offset(skip)
        .limit(limit)
    ]
    if not page_ite_ids:
        return {"total": 0, "scholars": []}

    base_query = (
        db.query(
            Scholar.scholar_id,
            Scholar.program_id,
            Scholar.status_id,
            Scholar.student_id,
            Scholar.first_name,
            Scholar.last_name,
            Scholar.preferred_name,
            Scholar.gender,
            Scholar.date_of_birth,
            Scholar.school_code,
            Scholar.dietary_pref_id,
            Scholar.dietary_allergy,
            Scholar.dietary_religious,
            Scholar.medical_allergies,
            Scholar.email,
            Scholar.program_start_date,
            Scholar.program_finish_date,
            Scholar.current_email,

            ScholarStatus.status_description,
            DietaryPreferences.dietary_pref_desc,
            ProgramDate.program_code,
            ProgramDate.program_year,
            ProgramDate.it1_start_date,
            ProgramDate.it1_weeks,
            ProgramDate.it2_start_date,
            ProgramDate.it2_weeks,
            ProgramDate.it3_start_date,
            ProgramDate.it3_weeks,
            GatewaySchool.school_id,
            GatewaySchool.school_name,
            GatewaySchool.is_gateway,

            ITEDetail.ite_id,
            ITEDetail.sponsor_name,
            ITEDetail.department,
            ITEDetail.ite_number,
            ITEDetail.prst_date,
            ITEDetail.ite_start,
            ITEDetail.ite_finish,
            ITEDetail.sponsor_evl_date,
            ITEDetail.completed_evl_date,
            ITEDetail.completed_form_date,
            ITEDetail.ite_detail_date,
            ITEDetail.ite_prst_date,
            ITEDetail.cocode,

            supervisor_subq.c.supervisors,
            ITEDashboardManual.indigenous_student,
            ITEDashboardManual.placement_performance,
            ITEDashboardManual.grade_updated,
            ITEDashboardManual.grade_updated_date,
            ITEDashboardManual.unsw_attendee_presentation,
            ITEDashboardManual.mpc_submitted_date,
            ITEDashboardManual.mpc_submitted_on_time,
            ITEDashboardManual.mpc_call_date,
            ITEDashboardManual.mpc_call_on_time,
            ITEDashboardManual.it_change_approved,


            pd_per_survey_subq.c.pd41_ts.label("pd41_ts"),
            pd_per_survey_subq.c.pd42_ts.label("pd42_ts"),
            pd_per_survey_subq.c.pd43_ts.label("pd43_ts"),


            case(
                (ITEDetail.ite_number == 1, pd_per_survey_subq.c.pd41_ts),
                (ITEDetail.ite_number == 2, pd_per_survey_subq.c.pd42_ts),
                (ITEDetail.ite_number == 3, pd_per_survey_subq.c.pd43_ts),
                else_=None
            ).label("pd_ts"),

            placement_grade_subq.c.placement_grade,
            student_comment_subq.c.student_comment,
        )
        .join(ScholarStatus, Scholar.status_id == ScholarStatus.status_id)
        .outerjoin(DietaryPreferences, Scholar.dietary_pref_id == DietaryPreferences.dietary_pref_id)
        .outerjoin(ProgramDate, Scholar.program_id == ProgramDate.program_code)
        .outerjoin(GatewaySchool, Scholar.school_code == GatewaySchool.school_id)
        .join(ITEDetail, Scholar.scholar_id == ITEDetail.scholar_id)
        .outerjoin(supervisor_subq, ITEDetail.ite_id == supervisor_subq.c.ite_id)
        .outerjoin(ITEDashboardManual, ITEDetail.ite_id == ITEDashboardManual.ite_id)

        .outerjoin(pd_per_survey_subq, Scholar.scholar_id == pd_per_survey_subq.c.scholar_id)
        .outerjoin(placement_grade_subq, Scholar.scholar_id == placement_grade_subq.c.scholar_id)
        .outerjoin(student_comment_subq, Scholar.scholar_id == student_comment_subq.c.scholar_id)
        .filter(ITEDetail.ite_id.in_(page_ite_ids))
    )

    rows = base_query.all()
    response = []

    for row in rows:
        program_year = row.program_year
        start_date = row.program_start_date

        from datetime import date, timedelta
        from dateutil.relativedelta import relativedelta


        scholar_type = None
        if start_date and program_year:
            feb_12 = date(program_year, 2, 12)
            aug_12 = date(program_year, 8, 12)
            if start_date.year > program_year:
                scholar_type = "REPLACEMENT"
            elif start_date <= feb_12:
                scholar_type = "ORIGINAL"
            elif start_date <= aug_12:
                scholar_type = "LATE APPOINTED"
            else:
                scholar_type = "LATE"


        ite_number = row.ite_number
        if ite_number == 1:
            latest_start_date = row.it1_start_date
            program_weeks = row.it1_weeks
        elif ite_number == 2:
            latest_start_date = row.it2_start_date
            program_weeks = row.it2_weeks
        elif ite_number == 3:
            latest_start_date = row.it3_start_date
            program_weeks = row.it3_weeks
        else:
            latest_start_date = None
            program_weeks = None

        if latest_start_date and program_weeks is not None:
            expected_finish_date = latest_start_date + timedelta(weeks=program_weeks)
        else:
            expected_finish_date = None

        if latest_start_date:
            latest_start_date = latest_start_date.date()
        if expected_finish_date:
            expected_finish_date = expected_finish_date.date()

        ite_start_date = row.ite_start
        if ite_start_date and latest_start_date:
            start_date_met = "YES" if ite_start_date <= latest_start_date + timedelta(weeks=1) else "NO"
        else:
            start_date_met = None

        ite_end_date = row.ite_finish
        if ite_end_date and expected_finish_date:
            end_date_met = "YES" if ite_end_date <= expected_finish_date + timedelta(weeks=1) else "NO"
        else:
            end_date_met = None


        pd_agreement_submission_date = row.pd_ts

        if ite_start_date and pd_agreement_submission_date:
            deadline = ite_start_date + timedelta(weeks=3)
            pd_on_time = "YES" if pd_agreement_submission_date <= deadline else "NO"
        else:
            pd_on_time = None

        if ite_start_date is None:
            placement_midpoint = None
        elif ite_number == 1:
            placement_midpoint = ite_start_date + relativedelta(months=2)
        elif program_weeks and program_weeks > 0:
            placement_midpoint = ite_start_date + timedelta(weeks=program_weeks / 2)
        else:
            placement_midpoint = None

        mpc_call_date = row.mpc_call_date
        mpc_due_date = mpc_call_date + timedelta(weeks=1) if mpc_call_date else None
        end_of_it_input_date = row.ite_prst_date
        end_of_it_presentation_date = row.prst_date
        if end_of_it_input_date and end_of_it_presentation_date:
            deadline = end_of_it_presentation_date + timedelta(weeks=2)
            it_presentation_ontime = "YES" if end_of_it_input_date <= deadline else "NO"
        else:
            it_presentation_ontime = None

        sponsor_sent_date = row.sponsor_evl_date
        if sponsor_sent_date and ite_end_date:
            sent_before_end = "YES" if sponsor_sent_date < ite_end_date else "NO"
        else:
            sent_before_end = None

        placement_completed_date = row.completed_evl_date
        if sponsor_sent_date and placement_completed_date:
            sponsor_eval_ontime = sponsor_sent_date < placement_completed_date
        else:
            sponsor_eval_ontime = None

        response.append(ScholarFullResponse(
            program=str(row.program_id) if row.program_id is not None else None,
            year=row.program_year,
            student_number=row.student_id,
            first_name=row.first_name,
            preferred_name=row.preferred_name,
            last_name=row.last_name,
            default_email=row.email,
            alternate_email=row.current_email,
            gender=row.gender,
            date_of_birth=row.date_of_birth,
            school_code=row.school_id,
            school_name=row.school_name,
            gateway_school="YES" if row.is_gateway == "Y" else "NO",
            indigenous_student=row.indigenous_student,
            university_status=row.status_description,
            start_date=row.program_start_date,
            end_date=row.program_finish_date,
            scholar_type=scholar_type,
            dietary_preference=row.dietary_pref_desc,
            dietary_allergies=row.dietary_allergy,
            dietary_religious=row.dietary_religious,
            medical_allergies=row.medical_allergies,
            ite_number=ite_number,
            sponsor_name=row.sponsor_name,
            department=row.department,
            supervisors=row.supervisors,
            ite_start=row.ite_start,
            ite_end=row.ite_finish,
            ite_details_input_date=row.ite_detail_date,
            latest_start_date=latest_start_date,
            expected_finish_date=expected_finish_date,
            start_date_met=start_date_met,
            end_date_met=end_date_met,
            it_change_approved=row.it_change_approved,
            pd_agreement_submission_date=pd_agreement_submission_date,  
            ite_input_in_time=pd_on_time,
            placement_mid_point=placement_midpoint,
            mpc_call_date=mpc_call_date,
            mpc_call_on_time=row.mpc_call_on_time,
            mpc_document_due_date=mpc_due_date,
            mpc_submitted_date=row.mpc_submitted_date,
            mpc_submitted_on_time=row.mpc_submitted_on_time,
            end_of_it_presentation_date=end_of_it_presentation_date,
            end_of_it_input_date=end_of_it_input_date,
            it_presentation_completed_ontime=it_presentation_ontime,
            unsw_attendee_on_presentation=row.unsw_attendee_presentation,
            sponsor_evaluation_sent_to_sponsor_date=sponsor_sent_date,
            sent_before_end_of_placement=sent_before_end,
            sponsor_completed_date=row.completed_form_date,
            placement_completed_date=placement_completed_date,
            placement_grade=row.placement_grade,
            placement_performance=row.placement_performance,
            grade_updated=row.grade_updated,
            grade_updated_date=row.grade_updated_date,
            scholar_comment=row.student_comment,
            sponsor_evaluation_completed_on_time=sponsor_eval_ontime
        ))

    return {
        "total": total,
        "scholars": response
    }





def list_scholar_dashboard(
    db: Session,
    skip: int = 0,
    limit: int = 10
) -> Dict[str, List[Dict]]:

    latest_ite_subq = (
        db.query(
            ITEDetail.scholar_id,
            ITEDetail.ite_start,
            ITEDetail.ite_finish,
            ITEDetail.sponsor_name,
            ITEDetail.ite_number,
            func.row_number()
            .over(
                partition_by=ITEDetail.scholar_id,
                order_by=ITEDetail.ite_start.desc()
            ).label("rn")
        )
    ).subquery()

    latest_ite = aliased(latest_ite_subq)

    base_query = (
        db.query(
            Scholar.student_id.label("student_number"),
            CoopProgram.program_id.label("program_id"), 
            Scholar.first_name,
            Scholar.program_start_date,
            Scholar.last_name,
            Scholar.current_email.label("email"),
            latest_ite.c.sponsor_name.label("most_recent_sponsor"),
            latest_ite.c.ite_start.label("most_recent_ite_start"),
            latest_ite.c.ite_finish.label("most_recent_ite_end"),
            Scholar.program_id.label("program_code"),
            ProgramDate.program_year,

        )
        .outerjoin(
            latest_ite,
            and_(
                Scholar.scholar_id == latest_ite.c.scholar_id,
                latest_ite.c.rn == 1
            )
        )
        .outerjoin(CoopProgram, Scholar.program_id == CoopProgram.program_code)
        .outerjoin(ProgramDate, Scholar.program_id == ProgramDate.program_code)
    )


    total = base_query.order_by(None).count()


    results = base_query.offset(skip).limit(limit).all()


    scholars = []
    for row in results:
        scholar_type = None
        if row.most_recent_ite_start and row.program_year:
            feb_12 = date(row.program_year, 2, 12)
            aug_12 = date(row.program_year, 8, 12)
            start_date = row.program_start_date

            if start_date  <= feb_12:
                scholar_type = "ORIGINAL"
            elif feb_12 < start_date  <= aug_12:
                scholar_type = "LATE APPOINTED"
            elif start_date  > aug_12:
                scholar_type = "LATE"
            elif start_date .year > row.program_year:
                scholar_type = "REPLACEMENT"

        scholars.append({
            "student_number": row.student_number,
            "program_id": row.program_id,
            "full_name": f"{row.first_name} {row.last_name}",
            "email": row.email,
            "scholar_type": scholar_type,
            "most_recent_sponsor": row.most_recent_sponsor,
            "most_recent_ite_start": row.most_recent_ite_start,
            "most_recent_ite_end": row.most_recent_ite_end,
        })

    return {
        "total": total,
        "scholars": scholars
    }


def list_scholar_dashboard_with_search(
    db: Session,
    skip: int = 0,
    limit: int = 10,
    keywords: Optional[str] = None,
) -> Dict[str, List[Dict]]:


    tokens: List[str] = []
    full_pattern = None
    if keywords:
        kw_str = keywords.strip()
        if kw_str:
            full_pattern = f"%{kw_str}%"
            tokens = [k for k in kw_str.split() if k]


    latest_ite_subq = (
        db.query(
            ITEDetail.scholar_id,
            ITEDetail.ite_start,
            ITEDetail.ite_finish,
            ITEDetail.sponsor_name,
            ITEDetail.ite_number,
            func.row_number()
            .over(
                partition_by=ITEDetail.scholar_id,
                order_by=ITEDetail.ite_start.desc()
            ).label("rn")
        )
    ).subquery()
    latest_ite = aliased(latest_ite_subq)


    if keywords:
        like_conditions = []


        for kw in tokens:
            pattern = f"%{kw}%"
            like_conditions.append(
                or_(
                    Scholar.student_id.ilike(pattern),
                    Scholar.program_id.ilike(pattern),
                    Scholar.first_name.ilike(pattern),
                    Scholar.last_name.ilike(pattern),
                    Scholar.current_email.ilike(pattern),
                    latest_ite.c.sponsor_name.ilike(pattern),
                    func.to_char(latest_ite.c.ite_start,  'YYYY-MM-DD').ilike(pattern),
                    func.to_char(latest_ite.c.ite_finish, 'YYYY-MM-DD').ilike(pattern),
                )
            )


        if full_pattern:
            full_name_expr = Scholar.first_name + ' ' + Scholar.last_name
            like_conditions.append(full_name_expr.ilike(full_pattern))

        search_subq = (
            db.query(Scholar.scholar_id.label("scholar_id"))
            .outerjoin(
                latest_ite,
                and_(Scholar.scholar_id == latest_ite.c.scholar_id,
                     latest_ite.c.rn == 1)
            )
            .filter(or_(*like_conditions))
            .subquery()
        )


    base_query = (
        db.query(
            Scholar.student_id.label("student_number"),
            CoopProgram.program_id.label("program_id"),      
            Scholar.first_name,
            Scholar.last_name,
            Scholar.current_email.label("email"),
            latest_ite.c.sponsor_name.label("most_recent_sponsor"),
            latest_ite.c.ite_start.label("most_recent_ite_start"),
            latest_ite.c.ite_finish.label("most_recent_ite_end"),
            Scholar.program_id.label("program_code"),         # Program Code
            ProgramDate.program_year,
            Scholar.program_start_date
        )
        .outerjoin(
            latest_ite,
            and_(Scholar.scholar_id == latest_ite.c.scholar_id,
                 latest_ite.c.rn == 1)
        )
        .outerjoin(CoopProgram, Scholar.program_id == CoopProgram.program_code)
        .outerjoin(ProgramDate, Scholar.program_id == ProgramDate.program_code)
    )

    if keywords:
        base_query = base_query.join(
            search_subq,
            Scholar.scholar_id == search_subq.c.scholar_id
        )


    total: int = base_query.order_by(None).count()
    results = (
        base_query
        .order_by(Scholar.student_id)       
        .offset(skip)
        .limit(limit)
        .all()
    )


    scholars: List[Dict] = []
    for row in results:


        scholar_type = None
        if row.most_recent_ite_start and row.program_year:
            feb_12 = date(row.program_year, 2, 12)
            aug_12 = date(row.program_year, 8, 12)
            start_date = row.program_start_date

            if start_date  <= feb_12:
                scholar_type = "ORIGINAL"
            elif feb_12 < start_date  <= aug_12:
                scholar_type = "LATE APPOINTED"
            elif start_date  > aug_12:
                scholar_type = "LATE"
            elif start_date .year > row.program_year:
                scholar_type = "REPLACEMENT"

        scholars.append({
            "student_number": row.student_number,
            "program_id": row.program_id,
            "full_name": f"{row.first_name} {row.last_name}",
            "email": row.email,
            "scholar_type": scholar_type,
            "most_recent_sponsor": row.most_recent_sponsor,
            "most_recent_ite_start": row.most_recent_ite_start,
            "most_recent_ite_end": row.most_recent_ite_end,
        })

    return {"total": total, "scholars": scholars}



def update_ite_dashboard_manual_batch(db: Session, updates: List[ITEDashboardManualUpdate]):
    for data in updates:
        record = db.query(ITEDashboardManual).filter_by(ite_id=data.ite_id).first()
        if not record:
            continue
        for key, value in data.dict(exclude_unset=True).items():
            if key != "ite_id":
                setattr(record, key, value)
    db.commit()


def list_ite_dashboard_manual_paginated(
    db: Session,
    skip: int = 0,
    limit: int = 20,
) -> Dict[str, List[Dict]]:
  

    total: int = db.query(func.count(ITEDashboardManual.ite_id)).scalar()

    results = (
        db.query(
            ITEDashboardManual,
            Scholar.first_name,
            Scholar.last_name,
            Scholar.preferred_name,
            Scholar.gender,
        )
        .outerjoin(ITEDetail, ITEDetail.ite_id == ITEDashboardManual.ite_id)
        .outerjoin(Scholar, Scholar.scholar_id == ITEDetail.scholar_id)
        .order_by(ITEDashboardManual.ite_id)
        .offset(skip)
        .limit(limit)
        .all()
    )

    records: List[Dict] = [
        {
            "ite_id": r.ite_id,
            "indigenous_student": r.indigenous_student,
            "placement_performance": r.placement_performance,
            "grade_updated": r.grade_updated,
            "grade_updated_date": r.grade_updated_date,
            "unsw_attendee_presentation": r.unsw_attendee_presentation,
            "mpc_submitted_date": r.mpc_submitted_date,
            "mpc_submitted_on_time": r.mpc_submitted_on_time,
            "mpc_call_date": r.mpc_call_date,
            "mpc_call_on_time": r.mpc_call_on_time,
            "it_change_approved": r.it_change_approved,
            "first_name": first_name,
            "last_name": last_name,
            "preferred_name": preferred_name,
            "gender": gender,
        }
        for (r, first_name, last_name, preferred_name, gender) in results
    ]

    return {
        "total": total,
        "records": records,
    }